package com.bot.rf.sql.util;

import com.bot.rf.config.LibraryConfig;
import com.bot.rf.entity.BookInfo;
import com.bot.rf.excel.ExcelUtil;
import com.bot.rf.excel.InputExcel;
import com.bot.rf.util.FileUtil;

import java.io.File;
import java.sql.*;
import java.util.*;

import static com.bot.rf.util.MyLogger.LOGGER;
import static com.bot.rf.util.MyLogger.getTrace;

/**
 * @program: report-form-demo
 * @description: sqlite工具类 操作本地db文件
 * @author: zyy
 * @create: 2021-02-25 11:44
 **/
public class LocalSqlUtil {

    private static Connection conn = null;
    private static Statement stmt = null;
    private static PreparedStatement pstmt = null;
    private static ResultSet resultSet = null;

    /**
     * 本地数据库db文件位置  /data/BookInfo.db
     */
    private static String URL = "jdbc:sqlite:data" + File.separator + "BookInfo.db";

    /**
     * 表名称
     */
    private static String TABLE_NAME = "bookInfo";

    //--------------------------- 配置方法 start -----------------------------
    /**
     * 创建连接
     * @return
     */
    public static boolean createOrConnect() {
        try {
            if (conn != null && !conn.isClosed()) {
                return true;
            }
            LOGGER.warn("数据库连接断开，重新连接...");
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection(URL);
        } catch (Exception e) {
            LOGGER.error(getTrace(e));
            return false;
        }
        createTable();
        return true;
    }

    /**
     * 关闭自动提交
     * @return
     */
    public static boolean closeAutoCommit() {
        try {
            if (conn == null || conn.isClosed()) {
                createOrConnect();
            }
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 提交
     * @return
     */
    public static boolean commit() {
        try {
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 创建 Statement
     * @return
     */
    public static boolean createStatement() {
        try {
            if (stmt != null && !stmt.isClosed()) {
                return true;
            }
            System.out.println("Sql Statement关闭，重新连接...");
            stmt = conn.createStatement();
        } catch (SQLException e) {
            LOGGER.error(getTrace(e));
            return false;
        }
        return true;
    }

    /**
     * 关闭连接
     */
    public static void closeConnect() {
        try {
            if (stmt != null && !stmt.isClosed()) {
                stmt.close();
            }
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }

    /**
     * 创建表
     */
    public static void createTable() {
        String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (EPC text, " +
                "Barcode text PRIMARY KEY NOT NULL, " +
                "BookName text, " +
                "BookIndex text, " +
                "CurrentLibrary text, " +
                "CurrentPosition text, " +
                "OrdinaryPosition text, " +
                "OrderNo text, " +
                "ShelfBlockNum text, " +
                "UpdateTime text, " +
                "ProcessType text, " +
                "FloorNumber text, " +
                "TemporaryLocationName text);";
        String sqlIndex = "CREATE INDEX IF NOT EXISTS " + LibraryConfig.LIBRARY_CODE + "_lib ON bookInfo (\n" +
                "    EPC,\n" +
                "    Barcode\n" +
                ");";
        try {
            if (conn == null || conn.isClosed()) {
                LOGGER.warn("数据库连接断开，重新连接...");
                createOrConnect();
            }
        } catch (SQLException e) {
            LOGGER.error(getTrace(e));
        }
        try {
            stmt = conn.createStatement();
            stmt.execute(sql);
            stmt.execute(sqlIndex);
        } catch (SQLException e) {
            LOGGER.error(getTrace(e));
        }
    }
    //--------------------------- 配置方法  end  -------------------------------

    //--------------------------- 自定义方法 start -----------------------------
    /**
     * 1、根据条形码查询本地数据库中图书信息
     *    数据库里存的是barcode，用barcode查询
     *           存的是epc，就用epc查询，灵活修改
     * @param tagId
     * @return
     */
    public static BookInfo queryBook(String tagId) {
        // 创建连接
        createOrConnect();
        // 创建Statement
        createStatement();
        String sql = "SELECT Barcode,BookName,BookIndex,OrdinaryPosition,ProcessType FROM " + TABLE_NAME + " WHERE EPC='" + tagId + "'";
        BookInfo bookInfo = new BookInfo();
        try {
            resultSet = stmt.executeQuery(sql);
            if (resultSet.next()) {
                String barcode = resultSet.getString("Barcode");
                String bookName = resultSet.getString("BookName");
                String bookIndex = resultSet.getString("BookIndex");
                String bookStatus = resultSet.getString("ProcessType");
                String ordinaryPosition = resultSet.getString("OrdinaryPosition");
                bookInfo.setTagId(tagId);
                bookInfo.setBarcode(barcode);
                bookInfo.setBookName(bookName);
                bookInfo.setBookIndex(bookIndex);
                bookInfo.setProcessType(bookStatus);
                bookInfo.setTemporaryLocationName(ordinaryPosition);
            } else {
                return null;
            }
            resultSet.close();
        } catch (Exception e) {
            LOGGER.error(getTrace(e));
            return null;
        }
        return bookInfo;
    }


    /**
     * 2、根据条形码查询本地数据库中图书信息
     *    数据库里存的是barcode，用barcode查询
     *           存的是epc，就用epc查询，灵活修改
     * @param barcode
     * @return
     */
    public static BookInfo queryByBarcode(String barcode) {
        // 创建连接
        createOrConnect();
        // 创建Statement
        createStatement();
        String sql = "SELECT EPC,BookName,BookIndex,OrdinaryPosition,ProcessType FROM " + TABLE_NAME + " WHERE Barcode='" + barcode + "'";
        BookInfo bookInfo = new BookInfo();
        try {
            resultSet = stmt.executeQuery(sql);
            if (resultSet.next()) {
                String tagId = resultSet.getString("EPC");
                String bookName = resultSet.getString("BookName");
                String bookIndex = resultSet.getString("BookIndex");
                String bookStatus = resultSet.getString("ProcessType");
                String ordinaryPosition = resultSet.getString("OrdinaryPosition");
                bookInfo.setTagId(tagId);
                bookInfo.setBarcode(barcode);
                bookInfo.setBookName(bookName);
                bookInfo.setBookIndex(bookIndex);
                bookInfo.setProcessType(bookStatus);
                bookInfo.setTemporaryLocationName(ordinaryPosition);
            } else {
                return null;
            }
            resultSet.close();
        } catch (Exception e) {
            System.out.println(barcode);
            LOGGER.error(getTrace(e));
            return null;
        }
        return bookInfo;
    }

    /**
     * 3、批量插入
     * @param bookInfoList
     */
    public static void updateBookInfo(List<BookInfo> bookInfoList){
        createOrConnect();
        closeAutoCommit();
        String sql = "update bookInfo SET EPC=?, CurrentPosition=? WHERE Barcode=?";
        try {
            pstmt = conn.prepareStatement(sql);
            // 批量插入
            for (BookInfo bookInfo:bookInfoList) {
                pstmt.setString(1, bookInfo.getTagId());
                pstmt.setString(2, LibraryConfig.getBookShelfIndexCode(bookInfo.getLocation()));
                pstmt.setString(3, bookInfo.getBarcode());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            commit();
            closeConnect();
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }

    /**
     * 删除数据库
     */
    public static void delete(){
        createOrConnect();
        closeAutoCommit();
        String sql = "delete from bookInfo WHERE Barcode != '123456' ";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.execute();
            commit();
            closeConnect();
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }



    /**
     * 4、批量更新本地库
     *    replace 存在则更新，否则插入
     *
     * @return
     */
    public static void batchUpdateBookInfo(List<BookInfo> bookInfoList){
        // 创建连接
        createOrConnect();
        // 关闭自动提交（事务）
        closeAutoCommit();
        String sql = "insert INTO bookInfo(epc, barcode, bookName, bookIndex,OrdinaryPosition,ProcessType " +
                ")" +
                " VALUES(?,?,?,?,?,?);";
        try {
            pstmt = conn.prepareStatement(sql);
            // 批量插入
            for (BookInfo bookInfo:bookInfoList) {
                pstmt.setString(1, bookInfo.getTagId());
                pstmt.setString(2, bookInfo.getBarcode());
                pstmt.setString(3, bookInfo.getBookName());
                pstmt.setString(4, bookInfo.getBookIndex());
                pstmt.setString(5, bookInfo.getTemporaryLocationName());
                pstmt.setString(6, bookInfo.getProcessType());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            commit();
            closeConnect();
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }
    //--------------------------- 自定义方法  end  -----------------------------


    public static void main(String[] args) {
//        List<InputExcel> excels = ExcelUtil.inputExcel("E:\\华师大数据.xlsx");
//        List<BookInfo> list = new ArrayList<>();
//        for (InputExcel e:excels) {
//            BookInfo bookInfo = new BookInfo();
//            bookInfo.setBookName(e.getBookName());
//            bookInfo.setTagId(e.getTagId());
//            bookInfo.setBarcode(e.getBarcode());
//            bookInfo.setBookIndex(e.getBookIndex());
//            bookInfo.setTemporaryLocationName(e.getLocation());
//            bookInfo.setProcessType(e.getBookStatus());
//            list.add(bookInfo);
//        }
//        batchUpdateBookInfo(list);
        delete();

//        List<String> tagIdS = FileUtil.readFileByLine("E:\\res.txt");
//        System.out.println(tagIdS.size());
//        delete(tagIdS);

    }
}
